﻿using System;
using System.Collections.Generic;
using System.Text;
using SMBL.Interface.Database;
using System.Data;
using System.Data.OleDb;

namespace SMBL
{
    namespace ADBS.Providers
    {
        /// <summary>
        /// Provider Type as SQL
        /// An abstract layer to use 
        /// Microsoft Access Database
        /// @Author: Push
        /// </summary>
        class OLEDB : IDBProvider
        {
            //////////////////////////////////////////////////////////////////////////
            // Parameters
            //////////////////////////////////////////////////////////////////////////

            // Global Connection String
            private String myConnectionString;
            // Connection statue
            public Boolean HasConnected
            {
                get
                {
                    return oleConn == null ? false : oleConn.State == ConnectionState.Open;
                    //return beConnected;
                }
            }
            // Connection
            private OleDbConnection oleConn;
            // Command
            private OleDbCommand oleCmd;

            //////////////////////////////////////////////////////////////////////////
            // Interface
            //////////////////////////////////////////////////////////////////////////

            #region IStart Members

            /// <summary>
            /// Create the connection
            /// @Author: Push
            /// </summary>
            /// <returns></returns>
            public bool Start()
            {
                // Check if current provider has connected to the database
                if (this.HasConnected) return true;
                // Create the connection
                this.oleConn = new OleDbConnection(myConnectionString);
                // on error throw any exception
                // will be caught by ErrorSystem
                this.oleConn.Open();
                // Command
                this.oleCmd = new OleDbCommand();
                this.oleCmd.Connection = this.oleConn;
                return true;
            }

            #endregion

            #region IEnd Members

            /// <summary>
            /// Close the connection
            /// @Author: Push
            /// </summary>
            /// <returns></returns>
            public bool End()
            {
                if (!this.HasConnected) return true;
                // Release the connection
                this.oleConn.Close();

                return true;
            }

            #endregion

            #region IProducer Members

            /// <summary>
            /// Execute the Stored Producer
            /// @Author: Push
            /// </summary>
            /// <param name="ProducerName">The name of the Stored Producer</param>
            /// <returns>A DataSet</returns>
            public System.Data.DataSet Producer(string ProducerName)
            {
                // Create the command and set command type as stored procedure
                oleCmd = new OleDbCommand(ProducerName, this.oleConn);
                oleCmd.CommandType = CommandType.StoredProcedure;
                // Use Adapter
                OleDbDataAdapter tmpAdapter = new OleDbDataAdapter(oleCmd);
                DataSet rtnDataSet = new DataSet();
                // Fill the DataSet
                tmpAdapter.Fill(rtnDataSet);

                // Reset
                oleCmd.CommandType = CommandType.Text;
                return rtnDataSet;
            }

            /// <summary>
            /// Execute the Stored Producer with Parameters
            /// @Author: Push
            /// </summary>
            /// <param name="ProducerName">The Name of the Stored Producer</param>
            /// <param name="ParameterValue">The Parameter list</param>
            /// <returns></returns>
            public System.Data.DataSet Producer(string ProducerName, List<KeyValuePair<string, object>> ParameterValue)
            {
                // Create the command and set command type as stored procedure
                oleCmd = new OleDbCommand(ProducerName, this.oleConn);
                oleCmd.CommandType = CommandType.StoredProcedure;

                // Fill up the parameters
                for (int i = 0; i < ParameterValue.Count; ++i)
                {
                    KeyValuePair<String, Object> tmpPair = ParameterValue[i];
                    OleDbParameter tmpParam = new OleDbParameter(tmpPair.Key, tmpPair.Value);
                    oleCmd.Parameters.Add(tmpParam);
                }

                // Use Adapter
                OleDbDataAdapter tmpAdapter = new OleDbDataAdapter(oleCmd);
                DataSet rtnDataSet = new DataSet();
                // Fill the DataSet
                tmpAdapter.Fill(rtnDataSet);

                // Reset
                oleCmd.CommandType = CommandType.Text;
                return rtnDataSet;
            }

            #endregion

            #region IConnectString Members

            /// <summary>
            /// Set connection string of current provider
            /// Do nothing if current provider has connected to the database
            /// @Author: Push
            /// </summary>
            /// <param name="TheConnString"></param>
            public void SetConnectionString(string TheConnString)
            {
                // Current Provider has connected to the database correctly
                if (this.HasConnected) return;

                this.myConnectionString = TheConnString;
            }

            #endregion

            #region IDBBasic Members

            /// <summary>
            /// Execute an SQL Sentence
            /// @Author: Push
            /// </summary>
            /// <param name="theSql"></param>
            /// <returns></returns>
            public int Execute(ISql theSql)
            {
                oleCmd = new OleDbCommand(theSql.GetSql(), this.oleConn);
                return oleCmd.ExecuteNonQuery();
            }

            /// <summary>
            /// Query according to the SQL Sentence
            /// @Author: Push
            /// </summary>
            /// <param name="theSql"></param>
            /// <returns></returns>
            public DataSet Query(ISql theSql)
            {
                // Use SqlDataAdapter to execute the query
                OleDbDataAdapter tmpAdapter = new OleDbDataAdapter(theSql.GetSql(), this.oleConn);
                DataSet rtnDataSet = new DataSet();
                // Fill the dataset
                tmpAdapter.Fill(rtnDataSet);
                return rtnDataSet;
            }

            #endregion

            #region ITransaction Members

            /// <summary>
            /// Do the transaction 
            /// Invoke the delegate function
            /// @Author: Push
            /// </summary>
            /// <param name="DelegateTransaction"></param>
            /// <returns></returns>
            public bool Transaction(delDoTransaction DelegateTransaction)
            {
                // Bind the transaction
                OleDbTransaction transAction = oleConn.BeginTransaction();
                oleCmd.Transaction = transAction;

                try
                {
                    // Do the methods
                    DelegateTransaction();
                    transAction.Commit();
                }
                catch (System.Exception e)
                {
                    // Error!
                    transAction.Rollback();
                    // Throw this exception and let cc to get
                    throw e;
                }

                return true;
            }

            #endregion

            #region IAutoUpdate Members

            /// <summary>
            /// Auto Update the Data Table
            /// </summary>
            /// <param name="dataTable"></param>
            /// <returns></returns>
            public bool AutoUpdate(DataTable dataTable)
            {
                throw new NotImplementedException();
            }

            #endregion

            #region IProducer Members


            public DataSet Producer( string ProducerName, bool hasRtnValue, out object ReturnValue, params object [] Parameters )
            {
                oleCmd.CommandText = ProducerName;
                oleCmd.CommandType = CommandType.StoredProcedure;

                OleDbCommandBuilder.DeriveParameters( oleCmd );

                ReturnValue = System.Reflection.Missing.Value;

                // Fill parameters.
                int npcount = oleCmd.Parameters.Count;
                for ( int i = 1; i < npcount; ++i )
                {
                    oleCmd.Parameters [i].Value = Parameters [i - 1];
                }

                // Use Adapter
                OleDbDataAdapter tmpAdapter = new OleDbDataAdapter( oleCmd );
                DataSet rtnDataSet = new DataSet( );
                // Fill the DataSet
                tmpAdapter.Fill( rtnDataSet );

                if ( hasRtnValue ) ReturnValue = oleCmd.Parameters [0].Value;

                // Reset
                oleCmd.CommandType = CommandType.Text;
                return rtnDataSet;
            }

            public DataSet Producer( string ProducerName, out object ReturnValue, params object [] Parameters )
            {
                return Producer( ProducerName, true, out ReturnValue, Parameters );
            }

            public DataSet Producer( string ProducerName, params object [] Parameters )
            {
                object _obj;
                return Producer( ProducerName, false, out _obj, Parameters );
            }

            #endregion

        }
    }
}